﻿/*
{
"状态":1,
"名称":"导入-BI日期数据(手工维护部分)",
"作者":"马学兵",
"创建时间":"2019-02-19",
"功能":"导入-BI日期数据(手工维护部分)",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/

--前端调用
DECLARE @json nvarchar(max)

set @json = '{jsonobj}'  
 
/*
set @json='{
        "subs": [
            {"日期": "2019-01-01","对比日期": "x","平日类型":"","法定假日":"","其他假日":"","节气":"","系数":""}			
        ]
    }'
*/


if object_id('tempdb..#dt')<>0 
	drop table #dt
	 
select rowid , 
max(case when fieldname = '日期' then stringValue else '' end) as 日期,
max(case when fieldname = '对比日期' then stringValue else '' end) as 对比日期, 
max(case when fieldname = '平日类型' then stringValue else '' end) as 平日类型, 
max(case when fieldname = '法定假日' then stringValue else '' end) as 法定假日, 
max(case when fieldname = '其他假日' then stringValue else '' end) as 其他假日, 
max(case when fieldname = '节气' then stringValue else '' end) as 节气, 
max(case when fieldname = '系数' then stringValue else '' end) as 系数 
INTO #dt
from dbo.yiparse(@json,'subs') 
group by rowid

if exists(select 1 from #dt where 日期='')
begin 
	RAISERROR ('导入日期 存在为空的行,请检查', 16 ,1 ) ; 
	return 
end 

if exists(select 日期 from #dt GROUP BY 日期 HAVING COUNT(1)>1 )
begin 
	RAISERROR ('导入日期 存在重复的行,请检查', 16 ,1 ) ; 
	return 
end 

if exists(select 日期 from #dt  WHERE ISDATE(日期)=0 )
begin 
	RAISERROR ('导入日期 存在错误,请检查', 16 ,1 ) ; 
	return 
end 

if exists(select 日期 from #dt A LEFT JOIN EIDW.dbo.b_date B ON A.日期=B.keydate WHERE B.keydate IS  NULL  )
begin 
	RAISERROR ('导入日期 存在 不在b_date的行,请检查', 16 ,1 ) ; 
	return 
end 

UPDATE a SET 
daytype=平日类型,
 holidaytype=法定假日,
 otherholidaytype=其他假日,
 comparisondate=对比日期,
 coefficient=系数,
 solarterms=节气
FROM EIDW.dbo.b_date a
INNER JOIN #dt b ON a.keydate=b.日期
 
DROP TABLE #dt
